Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# ALTER SYSTEM

Purpose

Use the `ALTER` `SYSTEM` statement to dynamically alter your Oracle Database instance. The settings stay in effect as long as the database is mounted.

Prerequisites

You must have `ALTER` `SYSTEM` system privilege.

Syntax

alter_system::=

Description of the illustration alter_system.gif

(archive_log_clause ::=, checkpoint_clause::=, check_datafiles_clause::=, distributed_recov_clauses::=, end_session_clauses::=, quiesce_clauses::=, alter_system_security_clauses::=, shutdown_dispatcher_clause::=, alter_system_set_clause::=, alter_system_reset_clause::=)

archive_log_clause ::=

Description of the illustration archive_log_clause.gif

checkpoint_clause::=

Description of the illustration checkpoint_clause.gif

check_datafiles_clause::=

Description of the illustration check_datafiles_clause.gif

distributed_recov_clauses::=

Description of the illustration distributed_recov_clauses.gif

end_session_clauses::=

Description of the illustration end_session_clauses.gif

quiesce_clauses::=

Description of the illustration quiesce_clauses.gif

alter_system_security_clauses::=

Description of the illustration alter_system_security_clauses.gif

shutdown_dispatcher_clause::=

Description of the illustration shutdown_dispatcher_clause.gif

alter_system_set_clause::=

Description of the illustration alter_system_set_clause.gif

alter_system_reset_clause::=

Description of the illustration alter_system_reset_clause.gif

Semantics

archive_log_clause

The `archive_log_clause` manually archives redo log files or enables or disables automatic archiving. To use this clause, your instance must have the database mounted. The database can be either open or closed unless otherwise noted.

INSTANCE Clause

This clause is relevant only if you are using Oracle Database with the Real Application Clusters option. Specify the name of the instance for which you want the redo log file group to be archived. The instance name is a string of up to 80 characters. Oracle Database automatically determines the thread that is mapped to the specified instance and archives the corresponding redo log file group. If no thread is mapped to the specified instance, then Oracle Database returns an error.

In earlier releases, you could identify the redo log file group to be archived by thread as well as instance. That syntax is now supported for backward compatibility only. The `INSTANCE` clause achieves the same purpose and is easier to use.

SEQUENCE Clause

Specify `SEQUENCE` to manually archive the online redo log file group identified by the log sequence number `integer` in the specified thread. If you omit the `THREAD` parameter, then Oracle Database archives the specified group from the thread assigned to your instance.

CHANGE Clause

Specify `CHANGE` to manually archive the online redo log file group containing the redo log entry with the system change number (SCN) specified by `integer` in the specified thread. If the SCN is in the current redo log file group, then Oracle Database performs a log switch. If you omit the `THREAD` parameter, then Oracle Database archives the groups containing this SCN from all enabled threads.

You can use this clause only when your instance has the database open.

CURRENT Clause

Specify `CURRENT` to manually archive the current redo log file group of the specified thread, forcing a log switch. If you omit the `THREAD` parameter, then Oracle Database archives all redo log file groups from all enabled threads, including logs previous to current logs. You can specify `CURRENT` only when the database is open.

NOSWITCH Specify `NOSWITCH` if you want to manually archive the current redo log file group without forcing a log switch. This setting is used primarily with standby databases to prevent data divergence when the primary database shuts down. Divergence implies the possibility of data loss in case of primary database failure.

You can use the `NOSWITCH` clause only when your instance has the database mounted but not open. If the database is open, then this operation closes the database automatically. You must then manually shut down the database before you can reopen it.

GROUP Clause

Specify `GROUP` to manually archive the online redo log file group with the `GROUP` value specified by `integer`. You can determine the `GROUP` value for a redo log file group by querying the data dictionary view `DBA_LOG_GROUPS`. If you specify both the `THREAD` and `GROUP` parameters, then the specified redo log file group must be in the specified thread.

LOGFILE Clause

Specify `LOGFILE` to manually archive the online redo log file group containing the redo log file member identified by '`filename`'. If you specify both the `THREAD` and `LOGFILE` parameters, then the specified redo log file group must be in the specified thread.

If the database was mounted with a backup controlfile, then specify `USING` `BACKUP` `CONTROLFILE` to permit archiving of all online logfiles, including the current logfile.

Restriction on the LOGFILE clause You must archive redo log file groups in the order in which they are filled. If you specify a redo log file group for archiving with the `LOGFILE` parameter, and earlier redo log file groups are not yet archived, then Oracle Database returns an error.

NEXT Clause

Specify `NEXT` to manually archive the next online redo log file group from the specified thread that is full but has not yet been archived. If you omit the `THREAD` parameter, then Oracle Database archives the earliest unarchived redo log file group from any enabled thread.

ALL Clause

Specify `ALL` to manually archive all online redo log file groups from the specified thread that are full but have not been archived. If you omit the `THREAD` parameter, then Oracle Database archives all full unarchived redo log file groups from all enabled threads.

START Clause

In earlier releases, this clause enabled automatic archiving of redo log file groups for the thread assigned to your instance. This clause has been deprecated, because Oracle Database automatically enables automatic archiving of redo log file groups. This clause has no effect. If you specify it, Oracle Database writes a message to the alert log.

TO location Clause

Specify `TO` '`location`' to indicate the primary location to which the redo log file groups are archived. The value of this parameter must be a fully specified file location following the conventions of your operating system. If you omit this parameter, then Oracle Database archives the redo log file group to the location specified by the initialization parameters `LOG_ARCHIVE_DEST` or `LOG_ARCHIVE_DEST_``n`.

STOP Clause

In earlier releases, this clause disabled automatic archiving of redo log file groups for the thread assigned to your instance. This clause has been deprecated. It has no effect, and if you specify it, Oracle Database writes a message to the alert log.

checkpoint_clause

Specify `CHECKPOINT` to explicitly force Oracle Database to perform a checkpoint, ensuring that all changes made by committed transactions are written to datafiles on disk. You can specify this clause only when your instance has the database open. Oracle Database does not return control to you until the checkpoint is complete.

GLOBAL In a Real Application Clusters environment, this setting causes Oracle Database to perform a checkpoint for all instances that have opened the database. This is the default.

LOCAL In a Real Application Clusters environment, this setting causes Oracle Database to perform a checkpoint only for the thread of redo log file groups for the instance from which you issue the statement.

See Also: "Forcing a Checkpoint: Example"

check_datafiles_clause

In a distributed database system, such as a Real Application Clusters environment, this clause updates an instance's SGA from the database control file to reflect information on all online datafiles.

- Specify `GLOBAL` to perform this synchronization for all instances that have opened the database. This is the default.

- Specify `LOCAL` to perform this synchronization only for the local instance.

Your instance should have the database open.

end_session_clauses

The `end_session_clauses` give you several ways to end the current session.

DISCONNECT SESSION Clause

Use the `DISCONNECT` `SESSION` clause to disconnect the current session by destroying the dedicated server process (or virtual circuit if the connection was made by way of a Shared Sever). To use this clause, your instance must have the database open. You must identify the session with both of the following values from the `V$SESSION` view:

- For `integer1`, specify the value of the `SID` column.

- For `integer2`, specify the value of the `SERIAL#` column.

If system parameters are appropriately configured, then application failover will take effect.

- The `POST_TRANSACTION` setting allows ongoing transactions to complete before the session is disconnected. If the session has no ongoing transactions, then this clause has the same effect described for as `KILL` `SESSION`.

- The `IMMEDIATE` setting disconnects the session and recovers the entire session state immediately, without waiting for ongoing transactions to complete.

  - If you also specify `POST_TRANSACTION` and the session has ongoing transactions, then the `IMMEDIATE` keyword is ignored.

  - If you do not specify `POST_TRANSACTION`, or you specify `POST_TRANSACTION` but the session has no ongoing transactions, then this clause has the same effect as described for `KILL` `SESSION` `IMMEDIATE`.

    See Also: "Disconnecting a Session: Example"

KILL SESSION Clause

The `KILL` `SESSION` clause lets you mark a session as terminated, roll back ongoing transactions, release all session locks, and partially recover session resources. To use this clause, your instance must have the database open, and your session and the session to be terminated must be on the same instance. You must identify the session with both of the following values from the `V$SESSION` view:

- For `integer1`, specify the value of the `SID` column.

- For `integer2`, specify the value of the `SERIAL#` column.

If the session is performing some activity that must be completed, such as waiting for a reply from a remote database or rolling back a transaction, then Oracle Database waits for this activity to complete, marks the session as terminated, and then returns control to you. If the waiting lasts a minute, then Oracle Database marks the session to be terminated and returns control to you with a message that the session is marked to be terminated. The `PMON` background process then marks the session as terminated when the activity is complete.

Whether or not the session has an ongoing transaction, Oracle Database does not recover the entire session state until the session user issues a request to the session and receives a message that the session has been terminated.

See Also: "Terminating a Session: Example"

IMMEDIATE Specify `IMMEDIATE` to instruct Oracle Database to roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately.

distributed_recov_clauses

The `DISTRIBUTED` `RECOVERY` clause lets you enable or disable distributed recovery. To use this clause, your instance must have the database open.

ENABLE Specify `ENABLE` to enable distributed recovery. In a single-process environment, you must use this clause to initiate distributed recovery.

You may need to issue the `ENABLE` `DISTRIBUTED` `RECOVERY` statement more than once to recover an in-doubt transaction if the remote node involved in the transaction is not accessible. In-doubt transactions appear in the data dictionary view `DBA_2PC_PENDING`.

See Also: "Enabling Distributed Recovery: Example"

DISABLE Specify `DISABLE` to disable distributed recovery.

FLUSH SHARED_POOL Clause

The `FLUSH` `SHARED` `POOL` clause lets you clear all data from the shared pool in the system global area (SGA). The shared pool stores

- Cached data dictionary information and

- Shared SQL and PL/SQL areas for SQL statements, stored procedures, function, packages, and triggers.

This statement does not clear shared SQL and PL/SQL areas for items that are currently being executed. You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.

See Also: "Clearing the Shared Pool: Example"

FLUSH BUFFER_CACHE Clause

The `FLUSH` `BUFFER_CACHE` clause lets you clear all data from the buffer cache in the system global area (SGA).

Caution: This clause is intended for use only on a test database. Do not use this clause on a production database, because as a result of this statement, subsequent queries will have no hits, only misses.

This clause is useful if you need to measure the performance of rewritten queries or a suite of queries from identical starting points.

SWITCH LOGFILE Clause

The `SWITCH` `LOGFILE` clause lets you explicitly force Oracle Database to begin writing to a new redo log file group, regardless of whether the files in the current redo log file group are full. When you force a log switch, Oracle Database begins to perform a checkpoint but returns control to you immediately rather than when the checkpoint is complete. To use this clause, your instance must have the database open.

See Also: "Forcing a Log Switch: Example"

SUSPEND | RESUME

The `SUSPEND` clause lets you suspend all I/O (datafile, control file, and file header) as well as queries, in all instances, enabling you to make copies of the database without having to handle ongoing transactions.

Restrictions on SUSPEND and RESUME `SUSPEND` and `RESUME` are subject to the following restrictions:

- Do not use this clause unless you have put the database tablespaces in hot backup mode.

- Do not terminate the session that issued the `ALTER` `SYSTEM` `SUSPEND` statement. An attempt to reconnect while the system is suspended may fail because of recursive SQL that is running during the `SYS` login.

- If you start a new instance while the system is suspended, then that new instance will not be suspended.

The `RESUME` clause lets you make the database available once again for queries and I/O.

quiesce_clauses

Use the `QUIESCE` `RESTRICTED` and `UNQUIESCE` clauses to put the database in and take it out of the quiesced state. This state enables database administrators to perform administrative operations that cannot be safely performed in the presence of concurrent transactions, queries, or PL/SQL operations.

Note: The `QUIESCE` `RESTRICTED` clause is valid only if the Database Resource Manager is installed and only if the Resource Manager has been on continuously since database startup in any instances that have opened the database.

If multiple `QUIESCE` `RESTRICTED` or `UNQUIESCE` statements issue at the same time from different sessions or instances, then all but one will receive an error.

QUIESCE RESTRICTED

Specify `QUIESCE` `RESTRICTED` to put the database in the quiesced state. For all instances with the database open, this clause has the following effect:

- Oracle Database instructs the Database Resource Manager in all instances to prevent all inactive sessions (other than `SYS` and `SYSTEM`) from becoming active. No user other than `SYS` and `SYSTEM` can start a new transaction, a new query, a new fetch, or a new PL/SQL operation.

- Oracle Database waits for all existing transactions in all instances that were initiated by a user other than `SYS` or `SYSTEM` to finish (either commit or abort). Oracle Database also waits for all running queries, fetches, and PL/SQL procedures in all instances that were initiated by users other than `SYS` or `SYSTEM` and that are not inside transactions to finish. If a query is carried out by multiple successive OCI fetches, then Oracle Database does not wait for all fetches to finish. It waits for the current fetch to finish and then blocks the next fetch. Oracle Database also waits for all sessions (other than those of `SYS` or `SYSTEM`) that hold any shared resources (such as enqueues) to release those resources. After all these operations finish, Oracle Database places the database into quiesced state and finishes executing the `QUIESCE` `RESTRICTED` statement.

- If an instance is running in shared server mode, then Oracle Database instructs the Database Resource Manager to block logins (other than `SYS` or `SYSTEM`) on that instance. If an instance is running in non-shared-server mode, then Oracle Database does not impose any restrictions on user logins in that instance.

During the quiesced state, you cannot change the Resource Manager plan in any instance.

UNQUIESCE

Specify `UNQUIESCE` to take the database out of quiesced state. Doing so permits transactions, queries, fetches, and PL/SQL procedures that were initiated by users other than `SYS` or `SYSTEM` to be undertaken once again. The `UNQUIESCE` statement does not have to originate in the same session that issued the `QUIESCE` `RESTRICTED` statement.

alter_system_security_clauses

The `alter_system_security_clauses` let you control access to the instance.

RESTRICTED SESSION

The `RESTRICTED` `SESSION` clause lets you restrict logon to Oracle Database. You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.

- Specify `ENABLE` to allow only users with `RESTRICTED` `SESSION` system privilege to log on to Oracle Database. Existing sessions are not terminated.

  This clause applies only to the current instance. Therefore, in a Real Application Clusters environment, authorized users without the `RESTRICTED` `SESSION` system privilege can still access the database by way of other instances.

- Specify `DISABLE` to reverse the effect of the `ENABLE` `RESTRICTED` `SESSION` clause, allowing all users with `CREATE` `SESSION` system privilege to log on to Oracle Database. This is the default.

See Also: "Restricting Sessions: Example"

SET ENCRYPTION WALLET Clause

Use this clause to manage database access to information in the server wallet. Although this statement begins with the keyword `ALTER`, an `ALTER` `SYSTEM` `SET` `ENCRYPTION` `WALLET` statement is not a DDL clause. However, you cannot roll back such a statement.

OPEN When you specify this clause, the database uses the specified password to load information from the server wallet into memory for database access for the duration of the instance. This clause lets the database retrieve keys from the server wallet without an SSO wallet. If the server wallet is not available or is already open, the database returns an error.

CLOSE Use this clause to remove the server wallet information from memory.

SET ENCRYPTION KEY Clause

Use this clause to generate a new encryption key and to set it as the current transparent data encryption master key. This clause also loads information from the server wallet into memory for database access. The `certificate_id` is the integer that identifies the certificate. It is not required if you are using basic keys, but it is required if you are using PKI-based keys. You can find this value by querying the `CERT_ID` column of the `V$WALLET` dynamic performance view. For `password`, specify the password used to connect to the security module. If you specify an invalid `certificate_id` or password, then the database returns an error.

An `ALTER` `SYSTEM` `SET` `KEY` statement is a DDL statement and will automatically commit any pending transactions in the schema.

You must set both an encryption wallet and an encryption key to use the transparent data encryption feature.

See Also: Oracle Advanced Security Administrator's Guide for more information on using the server wallet and encryption keys and on transparent data encryption the description of the `CREATE` `TABLE` "ENCRYPT encryption_spec " for information on using that feature to encrypt table columns "Establishing a Wallet and Encryption Key"

shutdown_dispatcher_clause

The `SHUTDOWN` clause is relevant only if your system is using the shared server architecture of Oracle Database. It shuts down a dispatcher identified by `dispatcher_name`.

Note: Do not confuse this clause with the SQL*Plus command `SHUTDOWN`, which is used to shut down the entire database.

The `dispatcher_name` must be a string of the form '`D` `xxx`', where `xxx` indicates the number of the dispatcher. For a listing of dispatcher names, query the `NAME` column of the `V$DISPATCHER` dynamic performance view.

- If you specify `IMMEDIATE`, then the dispatcher stops accepting new connections immediately and Oracle Database terminates all existing connections through that dispatcher. After all sessions are cleaned up, the dispatcher process shuts down.

- If you do not specify `IMMEDIATE`, then the dispatcher stops accepting new connections immediately but waits for all its users to disconnect and for all its database links to terminate. Then it shuts down.

REGISTER Clause

Specify `REGISTER` to instruct the `PMON` background process to register the instance with the listeners immediately. If you do not specify this clause, then registration of the instance does not occur until the next time `PMON` executes the discovery routine. As a result, clients may not be able to access the services for as long as 60 seconds after the listener is started.

See Also: Oracle Database Concepts and Oracle Net Services Administrator's Guide for information on the `PMON` background process and listeners

alter_system_set_clause

The `alter_system_set_clause` lets you set or reset the value of any initialization parameter. The parameters are described in "Initialization Parameters and ALTER SYSTEM".

The ability to change initialization parameter values depends on whether you have started up the database with a traditional client-side initialization parameter file (pfile) or with a server parameter file (spfile). To determine whether you can change the value of a particular parameter, query the `ISSYS_MODIFIABLE` column of the `V$PARAMETER` dynamic performance view.

When setting a parameter value, you can specify additional settings as follows:

COMMENT

The `COMMENT` clause lets you associate a comment string with this change in the value of the parameter. If you also specify `SPFILE`, then this comment will appear in the parameter file to indicate the most recent change made to this parameter.

DEFERRED

The `DEFERRED` keyword sets or modifies the value of the parameter for future sessions that connect to the database. Current sessions retain the old value.

You must specify `DEFERRED` if the value of the `ISSYS_MODIFIABLE` column of `V$PARAMETER` for this parameter is `DEFERRED`. If the value of that column is `IMMEDIATE`, then the `DEFERRED` keyword in this clause is optional. If the value of that column is `FALSE`, then you cannot specify `DEFERRED` in this `ALTER` `SYSTEM` statement.

See Also: Oracle Database Reference for information on the `V$PARAMETER` dynamic performance view

SCOPE

The `SCOPE` clause lets you specify when the change takes effect. Scope depends on whether you started up the database using a client-side parameter file (pfile) or server parameter file (spfile).

MEMORY `MEMORY` indicates that the change is made in memory, takes effect immediately, and persists until the database is shut down. If you started up the database using a parameter file (pfile), then this is the only scope you can specify.

SPFILE `SPFILE` indicates that the change is made in the server parameter file. The new setting takes effect when the database is next shut down and started up again. You must specify `SPFILE` when changing the value of a static parameter that is described as not modifiable in Oracle Database Reference.

BOTH `BOTH` indicates that the change is made in memory and in the server parameter file. The new setting takes effect immediately and persists after the database is shut down and started up again.

If a server parameter file was used to start up the database, then `BOTH` is the default. If a parameter file was used to start up the database, then `MEMORY` is the default, as well as the only scope you can specify.

SID

The `SID` clause is relevant only in a Real Application Clusters environment. This clause lets you specify the SID of the instance where the value will take effect.

- Specify `SID` = `'*'` if you want Oracle Database to change the value of the parameter for all instances.

- Specify `SID` = `'sid'` if you want Oracle Database to change the value of the parameter only for the instance `sid`. This setting takes precedence over previous and subsequent `ALTER` `SYSTEM` `SET` statements that specify `SID` = `'*'`.

If you do not specify this clause:

- If the instance was started up with a pfile (client-side initialization parameter file), then Oracle Database assumes the SID of the current instance.

- If the instance was started up with an spfile (server parameter file), then Oracle Database assumes `SID` `=` `'*'`.

If you specify an instance other than the current instance, then Oracle Database sends a message to that instance to change the parameter value in the memory of that instance.

See Also: Oracle Database Reference for information about the `V$PARAMETER` view

alter_system_reset_clause

In a Real Application Clusters (RAC) environment, the `alter_system_reset_clause` gives you separate control for an individual instance over parameters that may have been set for all instances in a server parameter file. The `SCOPE` clause has the same behavior as described for the `alter_system_set_clause`.

SID Specify the `SID` clause to remove a previously specified setting of this parameter for your instance--that is, a previous `ALTER` `SYSTEM` `SET` ... `SID` = `'sid'` statement. (In a non-RAC environment, you can specify `SID = '*'` for this clause.) Your instance will assume the value of the parameter as specified in a previous or subsequent `ALTER` `SYSTEM` `SET` ... `SID` = `'*'` statement.

See Also: Oracle Real Application Clusters Administrator's Guide for information on setting parameter values for an individual instance in a Real Application Clusters environment

## Initialization Parameters and ALTER SYSTEM

You can change the value of many initialization parameters for the current instance, whether you have started the database with a traditional client-side parameter file (pfile) or with a server parameter file (spfile). Oracle Database Reference indicates these parameters in the "Modifiable" category of each parameter description. If you are using a pfile, then the change will persist only for the duration of the instance. However, if you have started the database with an spfile, then you can change the value of the parameter in the spfile itself, so that the new value will occur in subsequent instances.

Three lists follow:

- The first is a list of basic parameters. Database administrators should be familiar with and consider the setting for all of the basic parameters.

- The second list shows the initialization parameters by their functional category.

- The third list includes all initialization parameters in alphabetical order.

For a description of these parameters, please refer to Oracle Database Reference.

See Also: the following examples of using the `ALTER` `SYSTEM` statement: "Changing Licensing Parameters: Examples", "Enabling Query Rewrite: Example", "Enabling Resource Limits: Example", "Shared Server Parameters ", and "Changing Shared Server Settings: Examples"

Basic Parameters

`CLUSTER_DATABASE`
`COMPATIBLE`
`CONTROL_FILES`
`DB_BLOCK_SIZE`
`DB_CREATE_FILE_DEST`
`DB_CREATE_ONLINE_LOG_DEST_``n`
`DB_DOMAIN`
`DB_NAME`
`DB_RECOVERY_FILE_DEST`
`DB_RECOVERY_FILE_DEST_SIZE`
`DB_UNIQUE_NAME`
`INSTANCE_NUMBER`
`JOB_QUEUE_PROCESSES`
`LOG_ARCHIVE_DEST_``n`
`LOG_ARCHIVE_DEST_STATE_``n`
`NLS_LANGUAGE`
`NLS_TERRITORY`
`OPEN_CURSORS`
`PGA_AGGREGATE_TARGET`
`PROCESSES`
`REMOTE_LISTENER`
`REMOTE_LOGIN_PASSWORDFILE`
`ROLLBACK_SEGMENTS`
`SESSIONS`
`SGA_TARGET`
`SHARED_SERVERS`
`STAR_TRANSFORMATION_ENABLED`
`UNDO_MANAGEMENT`
`UNDO_TABLESPACE`

Parameters by Functional Category

- ANSI Compliance

    `BLANK_TRIMMING`

- Backup and Restore

    `BACKUP_TAPE_IO_SLAVES`
    `RECYCLEBIN`
    `TAPE_ASYNCH_IO`

- BFILEs

    `SESSION_MAX_OPEN_FILES`

- Buffer Cache and I/O

    `DB_``n` `K_CACHE_SIZE`
    `DB_BLOCK_BUFFERS`
    `DB_BLOCK_SIZE`
    `DB_CACHE_ADVICE`
    `DB_CACHE_SIZE`
    `DB_FILE_MULTIBLOCK_READ_COUNT`
    `DB_KEEP_CACHE_SIZE`
    `DB_RECYCLE_CACHE_SIZE`
    `DB_WRITER_PROCESSES`
    `DBWR_IO_SLAVES`
    `DISK_ASYNCH_IO`
    `FILESYSTEMIO_OPTIONS`
    `READ_ONLY_OPEN_DELAYED`
    `USE_INDIRECT_DATA_BUFFERS`

- Cursors and Library Cache

    `CURSOR_SHARING`
    `CURSOR_SPACE_FOR_TIME`
    `OPEN_CURSORS`
    `SESSION_CACHED_CURSORS`

- Database/Instance Identification

    `DB_DOMAIN`
    `DB_NAME`
    `INSTANCE_NAME`

- Diagnostics and Statistics

    `BACKGROUND_CORE_DUMP`
    `BACKGROUND_DUMP_DEST`
    `CORE_DUMP_DEST`
    `DB_BLOCK_CHECKING`
    `DB_BLOCK_CHECKSUM`
    `EVENT`
    `MAX_DUMP_FILE_SIZE`
    `SHADOW_CORE_DUMP`
    `STATISTICS_LEVEL`
    `TIMED_OS_STATISTICS`
    `TIMED_STATISTICS`
    `TRACE_ENABLED`
    `TRACEFILE_IDENTIFIER`
    `USER_DUMP_DEST`

- Distributed, Replication

    `COMMIT_POINT_STRENGTH`
    `DISTRIBUTED_LOCK_TIMEOUT`
    `GLOBAL_NAMES`
    `HS_AUTOREGISTER`
    `OPEN_LINKS`
    `OPEN_LINKS_PER_INSTANCE`
    `REPLICATION_DEPENDENCY_TRACKING`

- File Locations, Names, and Sizes

    `AUDIT_FILE_DEST`
    `BACKGROUND_CORE_DUMP`
    `BACKGROUND_DUMP_DEST`
    `CONTROL_FILES`
    `CORE_DUMP_DEST`
    `DB_CREATE_FILE_DEST`
    `DB_CREATE_ONLINE_LOG_DEST_``n`
    `DB_FILES`
    `DB_RECOVERY_FILE_DEST`
    `DB_RECOVERY_FILE_DEST_SIZE`
    `FILE_MAPPING`
    `IFILE`
    `LOG_ARCHIVE_DEST_``n`
    `SPFILE`

- Globalization

    `NLS_CALENDAR`
    `NLS_COMP`
    `NLS_CURRENCY`
    `NLS_DATE_FORMAT`
    `NLS_DATE_LANGUAGE`
    `NLS_DUAL_CURRENCY`
    `NLS_ISO_CURRENCY`
    `NLS_LANGUAGE`
    `NLS_LENGTH_SEMANTICS`
    `NLS_NCHAR_CONV_EXCP`
    `NLS_NUMERIC_CHARACTERS`
    `NLS_SORT`
    `NLS_TERRITORY`
    `NLS_TIMESTAMP_FORMAT`
    `NLS_TIMESTAMP_TZ_FORMAT`

- Java

    `JAVA_MAX_SESSIONSPACE_SIZE`
    `JAVA_POOL_SIZE`
    `JAVA_SOFT_SESSIONSPACE_LIMIT`

- Job Queues

    `JOB_QUEUE_PROCESSES`

- License Limits

    `LICENSE_MAX_SESSIONS`
    `LICENSE_MAX_USERS`
    `LICENSE_SESSIONS_WARNING`

- Memory

    `LARGE_POOL_SIZE`
    `SHARED_POOL_RESERVED_SIZE`
    `SHARED_POOL_SIZE`

- Miscellaneous

    `AQ_TM_PROCESSES`
    `COMPATIBLE`
    `FIXED_DATE`

- Networking

    `LOCAL_LISTENER`
    `REMOTE_LISTENER`
    `SERVICE_NAMES`

- Objects and LOBs

    `OBJECT_CACHE_MAX_SIZE_PERCENT`
    `OBJECT_CACHE_OPTIMAL_SIZE`

- OLAP

    `OLAP_PAGE_POOL_SIZE`

- Optimizer

    `OPTIMIZER_DYNAMIC_SAMPLING`
    `OPTIMIZER_FEATURES_ENABLE`
    `OPTIMIZER_INDEX_CACHING`
    `OPTIMIZER_INDEX_COST_ADJ`
    `OPTIMIZER_MODE`
    `OPTIMIZER_SECURE_VIEW_MERGING`
    `QUERY_REWRITE_ENABLED`
    `QUERY_REWRITE_INTEGRITY`
    `STAR_TRANSFORMATION_ENABLED`

- Parallel Execution

    `PARALLEL_ADAPTIVE_MULTI_USER`
    `PARALLEL_EXECUTION_MESSAGE_SIZE`
    `PARALLEL_MAX_SERVERS`
    `PARALLEL_MIN_PERCENT`
    `PARALLEL_MIN_SERVERS`
    `PARALLEL_THREADS_PER_CPU`

- PL/SQL

    `PLSQL_NATIVE_LIBRARY_DIR`
    `PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT`
    `PLSQL_V2_COMPATIBILITY`
    `REMOTE_DEPENDENCIES_MODE`
    `UTL_FILE_DIR`

- PL/SQL Compiler

    `PLSQL_CC_FLAGS`
    `PLSQL_CODE_TYPE`
    `PLSQL_DEBUG`
    `PLSQL_OPTIMIZE_LEVEL`
    `PLSQL_WARNINGS`
    `NLS_LENGTH_SEMANTICS`

- SGA Memory

    `DB_``n` `K_CACHE_SIZE`
    `DB_CACHE_ADVICE`
    `DB_CACHE_SIZE`
    `HI_SHARED_MEMORY_ADDRESS`
    `JAVA_POOL_SIZE`
    `LOCK_SGA`
    `OLAP_PAGE_POOL_SIZE`
    `PGA_AGGREGATE_TARGET`
    `PRE_PAGE_SGA`
    `SGA_MAX_SIZE`
    `SHARED_MEMORY_ADDRESS`

- Real Application Clusters

    `ACTIVE_INSTANCE_COUNT`
    `CLUSTER_DATABASE`
    `CLUSTER_DATABASE_INSTANCES`
    `CLUSTER_INTERCONNECTS`
    `GC_FILES_TO_LOCKS`
    `INSTANCE_GROUPS`
    `INSTANCE_NUMBER`
    `MAX_COMMIT_PROPAGATION_DELAY`
    `PARALLEL_INSTANCE_GROUP`
    `THREAD`

- Redo Logs, Archiving, and Recovery

    `CONTROL_FILE_RECORD_KEEP_TIME`
    `DB_CREATE_ONLINE_LOG_DEST_``n`
    `DB_RECOVERY_FILE_DEST`
    `DB_RECOVERY_FILE_DEST_SIZE`
    `FAST_START_MTTR_TARGET`
    `LOG_BUFFER`
    `LOG_CHECKPOINT_INTERVAL`
    `LOG_CHECKPOINT_TIMEOUT`
    `LOG_CHECKPOINTS_TO_ALERT`
    `LOG_ARCHIVE_CONFIG`
    `LOG_ARCHIVE_DEST_``n`
    `LOG_ARCHIVE_DEST_STATE_``n`
    `LOG_ARCHIVE_DUPLEX_DEST`
    `LOG_ARCHIVE_FORMAT`
    `LOG_ARCHIVE_MAX_PROCESSES`
    `LOG_ARCHIVE_MIN_SUCCEED_DEST`
    `LOG_ARCHIVE_TRACE`
    `RECOVERY_PARALLELISM`

- Resource Manager

    `RESOURCE_LIMIT`
    `RESOURCE_MANAGER_PLAN`

- Security and Auditing

    `AUDIT_FILE_DEST`
    `AUDIT_SYS_OPERATIONS`
    `AUDIT_SYSLOG_LEVEL`
    `AUDIT_TRAIL`
    `O7_DICTIONARY_ACCESSIBILITY`
    `OS_AUTHENT_PREFIX`
    `OS_ROLES`
    `RDBMS_SERVER_DN`
    `REMOTE_LOGIN_PASSWORDFILE`
    `REMOTE_OS_AUTHENT`
    `REMOTE_OS_ROLES`
    `SQL92_SECURITY`

- Sessions and Processes

    `CPU_COUNT`
    `PROCESSES`
    `SESSIONS`

- Shared Server Architecture

    `CIRCUITS`
    `DISPATCHERS`
    `MAX_DISPATCHERS`
    `MAX_SHARED_SERVERS`
    `SHARED_SERVER_SESSIONS`
    `SHARED_SERVERS`

- Standby Database

    `ARCHIVE_LAG_TARGET`
    `DB_FILE_NAME_CONVERT`
    `DB_UNIQUE_NAME`
    `DG_BROKER_CONFIG_FILE` `n`
    `DG_BROKER_START`
    `FAL_CLIENT`
    `FAL_SERVER`
    `LOG_FILE_NAME_CONVERT`
    `STANDBY_ARCHIVE_DEST`
    `STANDBY_FILE_MANAGEMENT`

- Temporary Sort Space

    `BITMAP_MERGE_AREA_SIZE`
    `CREATE_BITMAP_AREA_SIZE`
    `HASH_AREA_SIZE`
    `PGA_AGGREGATE_TARGET`
    `SORT_AREA_RETAINED_SIZE`
    `SORT_AREA_SIZE`
    `WORKAREA_SIZE_POLICY`

- Transactions

    `COMMIT_WRITE`
    `DML_LOCKS`
    `FAST_START_PARALLEL_ROLLBACK`
    `TRANSACTIONS`

- Undo Management

    `RESUMABLE_TIMEOUT`
    `ROLLBACK_SEGMENTS`
    `TRANSACTIONS_PER_ROLLBACK_SEGMENT`
    `UNDO_MANAGEMENT`
    `UNDO_RETENTION`
    `UNDO_TABLESPACE`

All Parameters in Alphabetical Order

`ACTIVE_INSTANCE_COUNT`
`AQ_TM_PROCESSES`
`ARCHIVE_LAG_TARGET`
`ASM_DISKGROUPS`
`ASM_DISKSTRING`
`ASM_POWER_LIMIT`
`AUDIT_FILE_DEST`
`AUDIT_SYS_OPERATIONS`
`AUDIT_SYSLOG_LEVEL`
`AUDIT_TRAIL`
`BACKGROUND_CORE_DUMP`
`BACKGROUND_DUMP_DEST`
`BACKUP_TAPE_IO_SLAVES`
`BITMAP_MERGE_AREA_SIZE`
`BLANK_TRIMMING`
`CIRCUITS`
`CLUSTER_DATABASE`
`CLUSTER_DATABASE_INSTANCES`
`CLUSTER_INTERCONNECTS`
`COMMIT_POINT_STRENGTH`
`COMMIT_WRITE`
`COMPATIBLE`
`CONTROL_FILE_RECORD_KEEP_TIME`
`CONTROL_FILES`
`CORE_DUMP_DEST`
`CPU_COUNT`
`CREATE_BITMAP_AREA_SIZE`
`CREATE_STORED_OUTLINES`
`CURSOR_SHARING`
`CURSOR_SPACE_FOR_TIME`
`DB_``n` `K_CACHE_SIZE`
`DB_BLOCK_BUFFERS`
`DB_BLOCK_CHECKING`
`DB_BLOCK_CHECKSUM`
`DB_BLOCK_SIZE`
`DB_CACHE_ADVICE`
`DB_CACHE_SIZE`
`DB_CREATE_FILE_DEST`
`DB_CREATE_ONLINE_LOG_DEST_``n`
`DB_DOMAIN`
`DB_FILE_MULTIBLOCK_READ_COUNT`
`DB_FILE_NAME_CONVERT`
`DB_FILES`
`DB_FLASHBACK_RETENTION_TARGET`
`DB_KEEP_CACHE_SIZE`
`DB_NAME`
`DB_RECOVERY_FILE_DEST`
`DB_RECOVERY_FILE_DEST_SIZE`
`DB_RECYCLE_CACHE_SIZE`
`DB_UNIQUE_NAME`
`DB_WRITER_PROCESSES`
`DBWR_IO_SLAVES`
`DG_BROKER_CONFIG_FILE` `n`
`DG_BROKER_START`
`DISK_ASYNCH_IO`
`DISPATCHERS`
`DISTRIBUTED_LOCK_TIMEOUT`
`DML_LOCKS`
`EVENT`
`FAL_CLIENT`
`FAL_SERVER`
`FAST_START_MTTR_TARGET`
`FAST_START_PARALLEL_ROLLBACK`
`FILE_MAPPING`
`FILEIO_NETWORK_ADAPTERS`
`FILESYSTEMIO_OPTIONS`
`FIXED_DATE`
`GC_FILES_TO_LOCKS`
`GCS_SERVER_PROCESSES`
`GLOBAL_NAMES`
`HASH_AREA_SIZE`
`HI_SHARED_MEMORY_ADDRESS`
`HS_AUTOREGISTER`
`IFILE`
`INSTANCE_GROUPS`
`INSTANCE_NAME`
`INSTANCE_NUMBER`
`INSTANCE_TYPE`
`JAVA_MAX_SESSIONSPACE_SIZE`
`JAVA_POOL_SIZE`
`JAVA_SOFT_SESSIONSPACE_LIMIT`
`JOB_QUEUE_PROCESSES`
`LARGE_POOL_SIZE`
`LDAP_DIRECTORY_ACCESS`
`LICENSE_MAX_SESSIONS`
`LICENSE_MAX_USERS`
`LICENSE_SESSIONS_WARNING`
`LOCAL_LISTENER`
`LOCK_SGA`
`LOG_ARCHIVE_CONFIG`
`LOG_ARCHIVE_DEST`
`LOG_ARCHIVE_DEST_``n`
`LOG_ARCHIVE_DEST_STATE_``n`
`LOG_ARCHIVE_DUPLEX_DEST`
`LOG_ARCHIVE_FORMAT`
`LOG_ARCHIVE_LOCAL_FIRST`
`LOG_ARCHIVE_MAX_PROCESSES`
`LOG_ARCHIVE_MIN_SUCCEED_DEST`
`LOG_ARCHIVE_TRACE`
`LOG_BUFFER`
`LOG_CHECKPOINT_INTERVAL`
`LOG_CHECKPOINT_TIMEOUT`
`LOG_CHECKPOINTS_TO_ALERT`
`LOG_FILE_NAME_CONVERT`
`MAX_COMMIT_PROPAGATION_DELAY`
`MAX_DISPATCHERS`
`MAX_DUMP_FILE_SIZE`
`MAX_SHARED_SERVERS`
`NLS_CALENDAR`
`NLS_COMP`
`NLS_CURRENCY`
`NLS_DATE_FORMAT`
`NLS_DATE_LANGUAGE`
`NLS_DUAL_CURRENCY`
`NLS_ISO_CURRENCY`
`NLS_LANGUAGE`
`NLS_LENGTH_SEMANTICS`
`NLS_NCHAR_CONV_EXCP`
`NLS_NUMERIC_CHARACTERS`
`NLS_SORT`
`NLS_TERRITORY`
`NLS_TIMESTAMP_FORMAT`
`NLS_TIMESTAMP_TZ_FORMAT`
`O7_DICTIONARY_ACCESSIBILITY`
`OBJECT_CACHE_MAX_SIZE_PERCENT`
`OBJECT_CACHE_OPTIMAL_SIZE`
`OLAP_PAGE_POOL_SIZE`
`OPEN_CURSORS`
`OPEN_LINKS`
`OPEN_LINKS_PER_INSTANCE`
`OPTIMIZER_DYNAMIC_SAMPLING`
`OPTIMIZER_FEATURES_ENABLE`
`OPTIMIZER_INDEX_CACHING`
`OPTIMIZER_INDEX_COST_ADJ`
`OPTIMIZER_MODE`
`OPTIMIZER_SECURE_VIEW_MERGING`
`OS_AUTHENT_PREFIX`
`OS_ROLES`
`PARALLEL_ADAPTIVE_MULTI_USER`
`PARALLEL_EXECUTION_MESSAGE_SIZE`
`PARALLEL_INSTANCE_GROUP`
`PARALLEL_MAX_SERVERS`
`PARALLEL_MIN_PERCENT`
`PARALLEL_MIN_SERVERS`
`PARALLEL_THREADS_PER_CPU`
`PGA_AGGREGATE_TARGET`
`PLSQL_CCFLAGS`
`PLSQL_CODE_TYPE`
`PLSQL_DEBUG`
`PLSQL_NATIVE_LIBRARY_DIR`
`PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT`
`PLSQL_OPTIMIZE_LEVEL`
`PLSQL_V2_COMPATIBILITY`
`PLSQL_WARNINGS`
`PRE_PAGE_SGA`
`PROCESSES`
`QUERY_REWRITE_ENABLED`
`QUERY_REWRITE_INTEGRITY`
`RDBMS_SERVER_DN`
`READ_ONLY_OPEN_DELAYED`
`RECOVERY_PARALLELISM`
`RECYCLEBIN`
`REMOTE_DEPENDENCIES_MODE`
`REMOTE_LISTENER`
`REMOTE_LOGIN_PASSWORDFILE`
`REMOTE_OS_AUTHENT`
`REMOTE_OS_ROLES`
`REPLICATION_DEPENDENCY_TRACKING`
`RESOURCE_LIMIT`
`RESOURCE_MANAGER_PLAN`
`RESUMABLE_TIMEOUT`
`ROLLBACK_SEGMENTS`
`SERVICE_NAMES`
`SESSION_CACHED_CURSORS`
`SESSION_MAX_OPEN_FILES`
`SESSIONS`
`SGA_MAX_SIZE`
`SGA_TARGET`
`SHADOW_CORE_DUMP`
`SHARED_MEMORY_ADDRESS`
`SHARED_POOL_RESERVED_SIZE`
`SHARED_POOL_SIZE`
`SHARED_SERVER_SESSIONS`
`SHARED_SERVERS`
`SKIP_UNUSABLE_INDEXES`
`SMTP_OUT_SERVER`
`SORT_AREA_RETAINED_SIZE`
`SORT_AREA_SIZE`
`SPFILE`
`SQL92_SECURITY`
`SQLTUNE_CATEGORY`
`STANDBY_ARCHIVE_DEST`
`STANDBY_FILE_MANAGEMENT`
`STAR_TRANSFORMATION_ENABLED`
`STATISTICS_LEVEL`
`STREAMS_POOL_SIZE`
`TAPE_ASYNCH_IO`
`THREAD`
`TIMED_OS_STATISTICS`
`TIMED_STATISTICS`
`TRACE_ENABLED`
`TRACEFILE_IDENTIFIER`
`TRANSACTIONS`
`TRANSACTIONS_PER_ROLLBACK_SEGMENT`
`UNDO_MANAGEMENT`
`UNDO_RETENTION`
`UNDO_TABLESPACE`
`USE_INDIRECT_DATA_BUFFERS`
`USER_DUMP_DEST`
`UTL_FILE_DIR`
`WORKAREA_SIZE_POLICY`

## System Parameters and ALTER SYSTEM

This section describes system parameters. These parameters are not initialization parameters, so you cannot set them in a pfile or spfile. However, you can set them using an `ALTER` `SYSTEM` statement.

USE_STORED_OUTLINES

Syntax:

    USE_STORED_OUTLINES = { TRUE | FALSE | category_name }

The `USE_STORED_OUTLINES` parameter determines whether the optimizer will use stored public outlines to generate execution plans. `USE_STORED_OUTLINES` is not an initialization parameter.

- `TRUE` causes the optimizer to use outlines stored in the `DEFAULT` category when compiling requests.

- `FALSE` specifies that the optimizer should not use stored outlines. This is the default.

- `category_name` causes the optimizer to use outlines stored in the `category_name` category when compiling requests.

### Shared Server Parameters

When you start your instance, Oracle Database creates shared server processes and dispatcher processes for the shared server architecture based on the values of the `SHARED_SERVERS` and `DISPATCHERS` initialization parameters. You can also set the `SHARED_SERVERS` and `DISPATCHERS` parameters with `ALTER` `SYSTEM` to perform one of the following operations while the instance is running:

- Create additional shared server processes by increasing the minimum number of shared server processes.

- Terminate existing shared server processes after their current calls finish processing.

- Create more dispatcher processes for a specific protocol, up to a maximum across all protocols specified by the initialization parameter `MAX_DISPATCHERS`.

- Terminate existing dispatcher processes for a specific protocol after their current user processes disconnect from the instance.

Examples

Archiving Redo Logs Manually: Examples The following statement manually archives the redo log file group containing the redo log entry with the SCN 9356083:

    ALTER SYSTEM ARCHIVE LOG CHANGE 9356083;

The following statement manually archives the redo log file group containing a member named '`diskl:log6.log`' to an archived redo log file in the location '`diska:[arch$`]':

    ALTER SYSTEM ARCHIVE LOG
        LOGFILE 'diskl:log6.log'
        TO 'diska:[arch$]';

Enabling Query Rewrite: Example This statement enables query rewrite in all sessions for all materialized views for which query rewrite has not been explicitly disabled:

    ALTER SYSTEM SET QUERY_REWRITE_ENABLED = TRUE;

Restricting Sessions: Example You might want to restrict sessions if you are performing application maintenance and you want only application developers with `RESTRICTED` `SESSION` system privilege to log on. To restrict sessions, issue the following statement:

    ALTER SYSTEM
       ENABLE RESTRICTED SESSION;

You can then terminate any existing sessions using the `KILL` `SESSION` clause of the `ALTER` `SYSTEM` statement.

After performing maintenance on your application, issue the following statement to allow any user with `CREATE` `SESSION` system privilege to log on:

    ALTER SYSTEM
       DISABLE RESTRICTED SESSION;

Establishing a Wallet and Encryption Key The following statements load information from the server wallet into memory and set the transparent data encryption master key:

    ALTER SYSTEM SET ENCRYPTION WALLET OPEN AUTHENTICATED BY "welcome1";
    ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "welcome1";

These statements assume that you have initialized the security module and created a wallet with the password `welcome1`.

Clearing the Shared Pool: Example You might want to clear the shared pool before beginning performance analysis. To clear the shared pool, issue the following statement:

    ALTER SYSTEM FLUSH SHARED_POOL;

Forcing a Checkpoint: Example The following statement forces a checkpoint:

    ALTER SYSTEM CHECKPOINT;

Enabling Resource Limits: Example This `ALTER` `SYSTEM` statement dynamically enables resource limits:

    ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

Changing Shared Server Settings: Examples The following statement changes the minimum number of shared server processes to 25:

    ALTER SYSTEM SET SHARED_SERVERS = 25;

If there are currently fewer than 25 shared server processes, then Oracle Database creates more. If there are currently more than 25, then Oracle Database terminates some of them when they are finished processing their current calls if the load could be managed by the remaining 25.

The following statement dynamically changes the number of dispatcher processes for the TCP/IP protocol to 5 and the number of dispatcher processes for the ipc protocol to 10:

    ALTER SYSTEM
       SET DISPATCHERS =
          '(INDEX=0)(PROTOCOL=TCP)(DISPATCHERS=5)',
          '(INDEX=1)(PROTOCOL=ipc)(DISPATCHERS=10)';

If there are currently fewer than 5 dispatcher processes for TCP, then Oracle Database creates new ones. If there are currently more than 5, then Oracle Database terminates some of them after the connected users disconnect.

If there are currently fewer than 10 dispatcher processes for ipc, then Oracle Database creates new ones. If there are currently more than 10, then Oracle Database terminates some of them after the connected users disconnect.

If there are currently existing dispatchers for another protocol, then the preceding statement does not affect the number of dispatchers for that protocol.

Changing Licensing Parameters: Examples The following statement dynamically changes the limit on sessions for your instance to 64 and the warning threshold for sessions on your instance to 54:

    ALTER SYSTEM
       SET LICENSE_MAX_SESSIONS = 64
       LICENSE_SESSIONS_WARNING = 54;

If the number of sessions reaches 54, then Oracle Database writes a warning message to the `ALERT` file for each subsequent session. Also, users with `RESTRICTED` `SESSION` system privilege receive warning messages when they begin subsequent sessions.

If the number of sessions reaches 64, then only users with `RESTRICTED` `SESSION` system privilege can begin new sessions until the number of sessions falls below 64 again.

The following statement dynamically disables the limit for sessions on your instance. After you issue this statement, Oracle Database no longer limits the number of sessions on your instance.

    ALTER SYSTEM SET LICENSE_MAX_SESSIONS = 0;

The following statement dynamically changes the limit on the number of users in the database to 200. After you issue the preceding statement, Oracle Database prevents the number of users in the database from exceeding 200.

    ALTER SYSTEM SET LICENSE_MAX_USERS = 200;

Forcing a Log Switch: Example You might want to force a log switch to drop or rename the current redo log file group or one of its members, because you cannot drop or rename a file while Oracle Database is writing to it. The forced log switch affects only the redo log thread of your instance. The following statement forces a log switch:

    ALTER SYSTEM SWITCH LOGFILE;

Enabling Distributed Recovery: Example The following statement enables distributed recovery:

    ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;

You might want to disable distributed recovery for demonstration or testing purposes. You can disable distributed recovery in both single-process and multiprocess mode with the following statement:

    ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;

When your demonstration or testing is complete, you can then enable distributed recovery again by issuing an `ALTER` `SYSTEM` statement with the `ENABLE` `DISTRIBUTED` `RECOVERY` clause.

Terminating a Session: Example You might want to terminate the session of a user that is holding resources needed by other users. The user receives an error message indicating that the session has been terminated. That user can no longer make calls to the database without beginning a new session. Consider this data from the `V$SESSION` dynamic performance table, when the users `SYS` and `oe` both have open sessions:

    SELECT sid, serial#, username
       FROM v$session;

           SID    SERIAL# USERNAME
    ---------- ---------- ------------------------------
            29         85 SYS
            33          1
            35          8
            39         23 OE
            40          1
    . . .

The following statement terminates the session of the user `scott` using the `SID` and `SERIAL#` values from `V$SESSION`:

    ALTER SYSTEM KILL SESSION '39, 23';

Disconnecting a Session: Example The following statement disconnects user `scott`'s session, using the `SID` and `SERIAL#` values from `V$SESSION`:

    ALTER SYSTEM DISCONNECT SESSION '13, 8' POST_TRANSACTION;